using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Web;
using System.Web.Configuration;
using System.Web.UI;
using Microsoft.SharePoint;
using Microsoft.SharePoint.WebPartPages;

namespace PS.WebParts.Rollup
{
    public class SqlDataSourceView : ARollupDataSourceView
    {
        private readonly string confStrSectionName = "PS.Webparts.Rollup.SqlDataSourceConnection";
        private string dataSourceId;

        public SqlDataSourceView(HttpContext context, IDataSource owner, string viewName, string dataSourceId)
            : base(context, owner, viewName)
        {
            this.dataSourceId = dataSourceId;
        }

        public override DataTable RollUpData(SPWeb web)
        {
            if (RevertToSelf)
            {
                DataTable ret = null;
                SPSecurity.RunWithElevatedPrivileges(() =>
                {
                    ret = ComputeResults(web);
                }
                    );

                return ret;
            }
            return ComputeResults(web);
        }

        private DataTable ComputeResults(SPWeb web)
        {
            DataTable res = new DataTable("raw_sql_result");
            try
            {
                string confStrSection = confStrSectionName + "." + dataSourceId;
                string connectionString = WebConfigurationManager.ConnectionStrings[confStrSection].ConnectionString;

                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    connection.Open();
                    string query = ReplaceQueryLiveFields(SqlQuery, web, base.Context, string.Empty, base.Parameters);
                    SqlCommand command = new SqlCommand(query, connection);
                    SqlDataReader reader = command.ExecuteReader();
                    if (reader != null)
                    {
                        for (int i = 0; i < reader.FieldCount; i++)
                        {
                            res.Columns.Add(reader.GetName(i), typeof(string));
                        }

                        while (reader.Read())
                        {
                            var values = new object[reader.FieldCount];
                            reader.GetValues(values);
                            res.Rows.Add(values);
                        }
                    }
                }
            }
            catch (System.Exception e)
            {
                LogError(e.ToString());
            }

            return res;
        }
        protected override bool HasRootWebAccess(SPWeb web)
        {
            return true;
        }

        public string SqlQuery
        {
            get;
            set;
        }
        public bool RevertToSelf { get; set; }
    }

    [Designer("PS.WebParts.Rollup.Designers.CrossSiteDataSourceDesigner, PS.WebParts.Rollup.Designers, Version=1.0.0.0, Culture=neutral, PublicKeyToken=4f4a26fd125a021e")]
    public class SqlDataSource : RollUpDataSource
    {
        protected override ATabularDataSourceView CreateViewInstance()
        {
            SqlDataSourceView view = new SqlDataSourceView(base.Context, this, string.Empty, this.ID);
            return view;
        }

        [FriendlyName("Content Type"), Description(""), Browsable(true), DefaultValue("")]
        public string SqlQuery
        {
            get
            {
                return ((SqlDataSourceView) DSView).SqlQuery;
            }
            set
            {
                ((SqlDataSourceView) DSView).SqlQuery = value;
            }
        }

        [Browsable(true)]
        public bool RevertToSelf
        {
            get
            {
                return ((SqlDataSourceView)DSView).RevertToSelf;
            }
            set
            {
                ((SqlDataSourceView)DSView).RevertToSelf = value;
            }
        }
    }
}